﻿using BookManege.entity;
using MySql.Data.MySqlClient;
using System.Data;

namespace BookManege.service
{
    internal class BorrowManegeService
    {
        private MySqlConnection MySqlConnection;

        public BorrowManegeService()
        {
            this.MySqlConnection = DatabaseUtil.GetMysqlConnection();
        }
        //借书
        public int GetCount()
        {
            return Convert.ToInt32(DatabaseUtil.SelectUtil("SELECT count(1) as count FROM borrow"));
        }
        public DataTable GetBoorowList(int pageNum, int pageSize)
        {
            return DatabaseUtil.GetDataTable("select * from borrow limit " + ((pageNum - 1) * pageSize) + "," + pageSize);
        }
        public int DeleteBorrow(long borrowId)
        {
            return DatabaseUtil.MyExecuteNonQuery("delete from borrow where id =" + borrowId);
        }
        public int UpdateBorrow(Borrow borrow)
        {
            return DatabaseUtil.MyExecuteNonQuery("update borrow set book_id=" + borrow.BookId + ",start_date='" + borrow.StartDate +
                "',end_date='" + borrow.EndDate + "',is_still=" + borrow.IsStill + ",user_id=" + borrow.UserId + " where id = " + borrow.Id);
        }
        public DataTable Search(long borrowId,int isStill,DateTime startDate,DateTime endDate,long userId)
        {
            return DatabaseUtil.GetDataTable("select * from borrow where 1=1 " +
              (borrowId != 0 ? " and book_name like '%" + borrowId + "%'" : "") +
              (isStill != 0 ? " and is_still =" + isStill : "") +
              " and start_date >='" + startDate + "' and end_date<='" + endDate + "'" +
              (userId != 0 ? " and user_id =" + userId : ""));
        }
        //还书
        public DataTable BorrowList()
        {
            return DatabaseUtil.GetDataTable("select book_name,borrow.id as user_id from borrow" +
                " left join book on borrow.book_id=book.id where is_still = 0 and user_id =" + Program.user.Id);
        }
        public StillBook Search(string isbn,int borrowId)
        {
            string sql = "select borrow.id,book_id,book_name,start_date,borrow.number,Date(end_date)-CURDATE() as exceed FROM borrow LEFT JOIN " +
                        "book on book.id=borrow.book_id WHERE is_still=0 " + (string.IsNullOrEmpty(isbn) ? "" : " and ICBN='" + isbn+"'")
                        + (borrowId != 0 ? " and borrow.id = " + borrowId : "") + " and user_id =" + Program.user.Id;
            MySqlCommand mySqlCommand = new(sql, MySqlConnection);
            using MySqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader();
            StillBook stillBook = null;
            int isSubmit = Convert.ToInt32( DatabaseUtil.SelectUtil("select count(1) from still_application where 1=1 " + (borrowId != 0 ? " and borrow_id = " + borrowId : "")));
            if (mySqlDataReader.Read())
            {
                stillBook = new((long)mySqlDataReader.GetInt64("id"), (long)mySqlDataReader.GetInt64("book_id"), mySqlDataReader.GetString("book_name"),
                    mySqlDataReader.GetDateTime("start_date"),mySqlDataReader.GetInt32("number") ,mySqlDataReader.GetInt32("exceed"), isSubmit);
                mySqlDataReader.Close();
                return stillBook;
            }
            return null;
        }
        public int StillBook(long borrowId)
        {
            if( Convert.ToInt32(DatabaseUtil.SelectUtil("select count(1) from still_application where borrow_id=" + borrowId)) > 0)
            {
                return 250;
            }
            return DatabaseUtil.MyExecuteNonQuery("insert into still_application  (borrow_id,submit_date) values (" + borrowId + ",CURDATE())");
            //if (DatabaseUtil.MyExecuteNonQuery("update borrow set is_still=1,still_date=CURDATE() where id=" + borrowId) > 0)
            //{
            //    return DatabaseUtil.MyExecuteNonQuery("update book set number = number+" + number + " where id=" + bookId);
            //}
            //return 0;
        }
        //首页信息
        public int GetBorrowBookCount()
        {
            return Convert.ToInt32(DatabaseUtil.SelectUtil("SELECT sum(number) as count FROM borrow"));
        }
        public string GetBorrowLastDateBookName()
        {
            return (string)DatabaseUtil.SelectUtil("SELECT book_name from book LEFT JOIN borrow on  book.id = borrow.book_id ORDER BY borrow.start_date DESC");
        }
        public string GetBorrowMostBookName()
        {
            return (string)DatabaseUtil.SelectUtil("SELECT book_name FROM borrow left join book on book.id=borrow.book_id GROUP BY book_id");
        }
        public int GetStillCount()
        {
            return Convert.ToInt32(DatabaseUtil.SelectUtil("SELECT count(1) FROM borrow WHERE is_still = 1"));
        }
        public long BorrowTodayCount()
        {
            return (long)(DatabaseUtil.SelectUtil("SELECT count(1) FROM borrow WHERE DATE(still_date) = CURDATE()"));
        }
        public long StillTodatCount()
        {
            return (long)(DatabaseUtil.SelectUtil("SELECT count(1) FROM borrow WHERE DATE(start_date) = CURDATE()"));
        }
        public DataTable GetExceedList()
        {
            string sql = "SELECT book_name,user_name,start_date,end_date, CURDATE()-DATE(end_date) as exceed FROM borrow" +
                " LEFT JOIN `user` on borrow.user_id=user.id LEFT JOIN book on book.id=borrow.book_id WHERE CURDATE()-DATE(end_date)>0 and borrow.is_still!=1";
            MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sql, MySqlConnection);
            DataTable dataTable = new ();
            mySqlDataAdapter.Fill(dataTable);
            return dataTable;
        }
    }
}
